﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Globalization;
using Gacn.ControleAlunos.Library.Entities;
using Gacn.ControleAlunos.Library.DataAccess;

namespace Gacn.ControleAlunos.Library.DataAccess
{
    public static class AlunoDataAccess
    {

        #region Get Methods
        public static DataTable Get(long? id, long? codigo, string nome, string serie, bool? ativo)
        {
            SqlConnection connection = null;
            SqlCommand command = null;
            DataTable dataTable = null;


            connection = Util.GetConnection();
            connection.Open();

            command = connection.CreateCommand();
            command.CommandText = "uspConsultaPessoaPorId";
            command.CommandType = CommandType.StoredProcedure;

            command.Parameters.Add("idPessoa", SqlDbType.Int).Value = id;
            command.Parameters.Add("idTurma", SqlDbType.Int).Direction = ParameterDirection.Output;
            command.Parameters.Add("SUCESSO", SqlDbType.Bit).Direction = ParameterDirection.Output;
            command.Parameters.Add("ProcComment", SqlDbType.VarChar).Direction = ParameterDirection.Output;

            dataTable = new DataTable();
            dataTable.Locale = CultureInfo.InvariantCulture;
            new SqlDataAdapter(command).Fill(dataTable);

            if (!(bool)command.Parameters["SUCESSO"].Value)
            {
                throw new Exception(string.Concat("Ocorreu um erro ao executar a procedure. \r\n", command.Parameters["ProcComment"].Value.ToString()));
            }

            return dataTable;
        }

        public static DataTable GetAllAlunoEntity()
        {

            SqlConnection connection = null;
            SqlCommand command = null;
            DataTable dataTable = null;


            connection = Util.GetConnection();
            connection.Open();

            command = connection.CreateCommand();
            command.CommandText = "uspReportAlunosCurso";
            command.CommandType = CommandType.StoredProcedure;

            command.Parameters.Add("SUCESSO", SqlDbType.Bit).Direction = ParameterDirection.Output;
            command.Parameters.Add("ProcComment", SqlDbType.VarChar, 1000).Direction = ParameterDirection.Output;

            dataTable = new DataTable();
            dataTable.Locale = CultureInfo.InvariantCulture;
            new SqlDataAdapter(command).Fill(dataTable);

            if (!(bool)command.Parameters["SUCESSO"].Value)
            {
                throw new Exception(string.Concat("Ocorreu um erro ao executar a procedure. \r\n", command.Parameters["ProcComment"].Value.ToString()));
            }

            return dataTable;
        }

        public static DataTable GetAlunoEntityPorNome(string nomeAluno)
        {

            SqlConnection connection = null;
            SqlCommand command = null;
            DataTable dataTable = null;


            connection = Util.GetConnection();
            connection.Open();

            command = connection.CreateCommand();
            command.CommandText = "uspConsultaPessoaPorNome";
            command.CommandType = CommandType.StoredProcedure;

            command.Parameters.Add("NomePessoa", SqlDbType.VarChar).Value = nomeAluno;
            command.Parameters.Add("SUCESSO", SqlDbType.Bit).Direction = ParameterDirection.Output;
            command.Parameters.Add("ProcComment", SqlDbType.VarChar, 1000).Direction = ParameterDirection.Output;

            dataTable = new DataTable();
            dataTable.Locale = CultureInfo.InvariantCulture;
            new SqlDataAdapter(command).Fill(dataTable);

            if (!(bool)command.Parameters["SUCESSO"].Value)
            {
                throw new Exception(string.Concat("Ocorreu um erro ao executar a procedure. \r\n", command.Parameters["ProcComment"].Value.ToString()));
            }

            return dataTable;
        }

        public static DataTable GetAlunoEntityPorID(int idAluno)
        {

            SqlConnection connection = null;
            SqlCommand command = null;
            DataTable dataTable = null;


            connection = Util.GetConnection();
            connection.Open();

            command = connection.CreateCommand();
            command.CommandText = "uspConsultaPessoaPorID";
            command.CommandType = CommandType.StoredProcedure;

            command.Parameters.Add("idPessoa", SqlDbType.Int).Value = idAluno;
            command.Parameters.Add("idTurma", SqlDbType.Int).Direction = ParameterDirection.Output;

            command.Parameters.Add("SUCESSO", SqlDbType.Bit).Direction = ParameterDirection.Output;
            command.Parameters.Add("ProcComment", SqlDbType.VarChar, 1000).Direction = ParameterDirection.Output;
            
            dataTable = new DataTable();
            dataTable.Locale = CultureInfo.InvariantCulture;
            new SqlDataAdapter(command).Fill(dataTable);

            if (!(bool)command.Parameters["SUCESSO"].Value)
            {
                throw new Exception(string.Concat("Ocorreu um erro ao executar a procedure. \r\n", command.Parameters["ProcComment"].Value.ToString()));
            }

            return dataTable;
        }
        
        public static DataTable GetAllAlunoDetalhado()
        {
            SqlConnection connection = null;
            SqlCommand command = null;
            DataTable dataTable = null;


            connection = Util.GetConnection();
            connection.Open();

            command = connection.CreateCommand();
            command.CommandText = "SELECT tbPessoa.idPessoa, tbPessoa.NomePessoa, tbPessoa.Email, tbPessoa.idTipoPessoa, tbPessoa.DtNasc, tbPessoa.Sexo, tbPessoa.nrSapato, tbPessoa.nrRoupaBaixo, tbPessoa.nrRoupaCima, tbPessoa.nrFone, tbPessoa.nrFone2, tbPessoa.nrRA, tbPessoa.aluSerie, tbPessoa.dtInsert, tbPessoa.flgAtivo, tbPessoa.obs, tbPessoa.idInsertUser, tbTurma.idTurma, tbCurso.idCurso, tbCurso.NomeCurso, tbEscola.idEscola, tbEscola.NomeEscola, tbTurma.CodTurma FROM         tbPessoa INNER JOIN tbMatricula ON tbPessoa.idPessoa = tbMatricula.idPessoa INNER JOIN tbTurma ON tbMatricula.idTurma = tbTurma.idTurma INNER JOIN tbEscola ON tbTurma.idEscola = tbEscola.idEscola INNER JOIN tbCurso ON tbTurma.idCurso = tbCurso.idCurso";
            command.CommandType = CommandType.Text;

            dataTable = new DataTable();
            dataTable.Locale = CultureInfo.InvariantCulture;
            new SqlDataAdapter(command).Fill(dataTable);

            return dataTable;
        }

        public static DataTable UpdateMatricula(int AlunoID, int TurmaID)
        {
            SqlConnection connection = null;
            SqlCommand command = null;
            DataTable dataTable = null;


            connection = Util.GetConnection();
            connection.Open();

            command = connection.CreateCommand();
            command.CommandText = "uspUpdateMatricula";
            command.CommandType = CommandType.StoredProcedure;

            command.Parameters.Add("idAluno", SqlDbType.VarChar).Value = AlunoID;
            command.Parameters.Add("idTurma", SqlDbType.VarChar).Value = TurmaID;
            command.Parameters.Add("SUCESSO", SqlDbType.Bit).Direction = ParameterDirection.Output;
            command.Parameters.Add("ProcComment", SqlDbType.VarChar, 1000).Direction = ParameterDirection.Output;

            dataTable = new DataTable();
            dataTable.Locale = CultureInfo.InvariantCulture;
            new SqlDataAdapter(command).Fill(dataTable);

            return dataTable;
        }

        #endregion


        #region Insert Methods
        public static DataTable CadastraMatricula(int PessoaID, int TurmaID, string Observacao, int InsertUserID)
        {

            SqlConnection connection = null;
            SqlCommand command = null;
            DataTable dataTable = null;


            connection = Util.GetConnection();
            connection.Open();

            command = connection.CreateCommand();
            command.CommandText = "uspCadastraMatricula";
            command.CommandType = CommandType.StoredProcedure;

            command.Parameters.Add("idPessoa", SqlDbType.Int).Value = PessoaID;
            command.Parameters.Add("idTurma", SqlDbType.SmallInt).Value = TurmaID;
            command.Parameters.Add("obs", SqlDbType.VarChar).Value = Observacao;
            command.Parameters.Add("idInsertUser", SqlDbType.Int).Value = InsertUserID;
            command.Parameters.Add("dtMatricula", SqlDbType.DateTime).Value = DateTime.Today;

            command.Parameters.Add("SUCESSO", SqlDbType.Bit).Direction = ParameterDirection.Output;
            command.Parameters.Add("ProcComment", SqlDbType.VarChar, 1000).Direction = ParameterDirection.Output;

            dataTable = new DataTable();
            dataTable.Locale = CultureInfo.InvariantCulture;
            new SqlDataAdapter(command).Fill(dataTable);

            if (!(bool)command.Parameters["SUCESSO"].Value)
            {
                throw new Exception(string.Concat("Ocorreu um erro ao executar a procedure. \r\n", command.Parameters["ProcComment"].Value.ToString()));
            }

            return dataTable;
        }
        #endregion

    }
}
